package com.ce.pms.customer.dao.impl;

import java.util.List;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.annotation.AssociatedBuildingQuery;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.customer.dao.ICustomerDao;
import com.ce.pms.customer.model.ClientDO;
import com.ce.pms.customer.model.IndividualDO;
import com.ce.pms.customer.model.OrganizationDO;
import com.ce.pms.customer.model.ParticipantDO;
import com.ce.pms.customer.vo.CustSearchVO;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述:
 * <p>
 * 版权所有：中企动力
 * <p>
 * 未经本公司许可，不得以任何方式复制或使用本程序任何部分
 *
 * @author 付湘辉 Created on 2008-05-22
 */
public class CustomerDaoImpl extends PmsBaseDaoImpl<ParticipantDO> implements
		ICustomerDao {
	private static final String SQLFILENAME="CustomerServiceSQL.xml";
	private final String QUERY_RESIDENT_INFO="queryResidentInfo";
	private final String QUERY_CUSTOMER_INFO="queryCustomerInfo";
	private final String QUERY_HISRESIDENTS="queryHisResidents";

	private SqlConfig sqlConfig=null;

	public CustomerDaoImpl() {
		sqlConfig=new SqlConfig(SQLFILENAME);
	}

	/**
	 * Function:根据参与人的id查询客户实体
	 * @param
	 * @return
	 * Create author:xuhaidui
	 * Create on:2008-5-27
	 * Edit author:
	 * Edit on:
	 * Why:
	 */
	public ClientDO queryClientByParticipantId(final String hql,Long participantId){
		ClientDO clientDO = new ClientDO();
		Query query=super.createQuery(hql, participantId);
		if(query.list().size()>0) {
			clientDO=(ClientDO)query.list().get(0);
		}

		return clientDO;
	}

	/**
	 * 功能描述：居住用户查询。如要用按房号查询，因考虑到有管理房和产权房，故先调用方法查询出该房号的ID<br>
	 * @param vo
	 * @param pageNo
	 * @param pageSize
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-21<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	@AssociatedBuildingQuery(alias="H",beginStr=" AND ",endStr=" ",property4Building="BUILDING_ID",insertAfterPoint="WHERE 1=1")
	public Page queryResidentInfo(CustSearchVO vo,Integer pageNo, Integer pageSize) {
		String sql=sqlConfig.getSql(QUERY_RESIDENT_INFO);
		sql=sql.replaceAll("_searchCondtition", buildCondition(vo));
		sql=sql.replaceAll("_orderString", buildOrderStr(vo));
		return this.pagedSqlQueryByPageNo(sql, pageNo, pageSize);
	}

	/**
	 * 功能描述：居住用户查询。如要用按房号查询，因考虑到有管理房和产权房，
	 * 故先调用方法查询出该房号的ID<br>
	 * @param vo
	 * @param pageNo
	 * @param pageSize
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-21<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	@AssociatedBuildingQuery(alias="H",beginStr=" AND ",endStr=" ",property4Building="BUILDING_ID",insertAfterPoint="WHERE 1=1")
	public List<Object[]> queryResidentInfo(CustSearchVO vo) {
		String sql=sqlConfig.getSql(QUERY_RESIDENT_INFO);
		sql=sql.replaceAll("_searchCondtition", buildCondition(vo));
		sql=sql.replaceAll("_orderString", buildOrderStr(vo));
		return this.createSQLQuery(sql).list();
	}

	/**
	 * 功能描述:当前用户查询。如要用按房号查询，因考虑到有管理房和产权房，故先调用方法查询出该房号的ID<br>
	 * @param vo
	 * @param pageNo
	 * @param pageSize
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-21<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	@AssociatedBuildingQuery(alias = "H", beginStr = " AND ", endStr = " ",property4Building="BUILDING_ID", insertAfterPoint = "WHERE 1=1")
	public Page queryCustomerInfo(CustSearchVO vo, Integer pageNo, Integer pageSize){
		String sql=sqlConfig.getSql(QUERY_CUSTOMER_INFO);
		sql=sql.replaceAll("_searchCondtition", buildCondition(vo));
		sql=sql.replaceAll("_orderString", buildOrderStr(vo));
		return this.pagedSqlQueryByPageNo(sql, pageNo, pageSize);
	}

	/**
	 * 功能描述:居住历史查询。如要用按房号查询，因考虑到有管理房和产权房，故先调用方法查询出该房号的ID<br>
	 * @param vo
	 * @param pageNo
	 * @param pageSize
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-21<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	@AssociatedBuildingQuery(alias="H",beginStr="AND",endStr=" ",property4Building="BUILDING_ID",insertAfterPoint="WHERE 1=1")
	public Page queryHisResidents(CustSearchVO vo, Integer pageNO, Integer pageSize){
		String sql=sqlConfig.getSql(QUERY_HISRESIDENTS);
		sql=sql.replaceAll("_searchCondtition", buildCondition(vo));
		sql=sql.replaceAll("_orderString", buildOrderStr(vo));
		return this.pagedSqlQueryByPageNo(sql, pageNO, pageSize);
	}

	/**
	 * Function:根据证件类型ID和证件号码查询参与人信息。<br>
	 * 			将会查询这三个表：CS_PARTICIPANT,CS_INDIVIDUAL,CS_ID_TYPE<br>
	 * 			业务上决定这两个条件决定数据的唯一性。如果数据不存在或者不止一个或者出现异常都将返回null<br>
	 * @param idTypeId
	 * @param idNO
	 * @return
	 * Create author:罗军林
	 * Create on:2008-5-30
	 * Edit author:
	 * Edit on:
	 * Why:
	 */
	public IndividualDO queryParticipant(String idTypeId, String idNO) {
		String hql="FROM IndividualDO ind WHERE ind.idNumber=? and ind.idType.idTypeId=?";
		try{
			Query query=this.createQuery(hql.toString());
			query.setString(0, idNO);
			query.setString(1, idTypeId);
			List list=query.list();
			if(list.size()==1){
				return (IndividualDO)list.get(0);
			}else{
				return null;
			}
		}catch(Exception e){
			return null;
		}
	}

	/**
	 * Function:根据企业名称查询企业信息。<br>
	 * 			将会查询这两个表：CS_PARTICIPANT,CS_ORGANIZATION<br>
	 * 			业务上决定企业名称数据的唯一性。如果数据不存在或者不止一个或者出现异常都将返回null<br>
	 * @param participantName
	 * @return
	 * Create author:罗军林
	 * Create on:2008-5-30
	 * Edit author:
	 * Edit on:
	 * Why:
	 */
	public OrganizationDO queryOrganizationByName(String participantName) {
		String hql="FROM OrganizationDO odo WHERE odo.participantName=?";
		try{
			Query query=this.createQuery(hql.toString());
			query.setString(0, participantName);
			List list=query.list();
			if(list.size()==1){
				return (OrganizationDO)list.get(0);
			}else{
				return null;
			}
		}catch(Exception e){
			return null;
		}
	}


	/**
	 * 功能描述:<br>
	 * @param sql<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-17<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildOrderStr(CustSearchVO vo) {
		StringBuffer orderstr=new StringBuffer("ORDER BY ");
		String houseNumberOrderAsc=" HB.SEQ_NO ASC,IF(GR.HOUSE_GRP_NAME,CAST(GR.HOUSE_GRP_NAME AS SIGNED),1 ) ASC,FL.STOREY_NUMBER ASC,H.HOUSE_NUMBER ASC";
		String houseNumberOrderDesc=" HB.SEQ_NO DESC,IF(GR.HOUSE_GRP_NAME,CAST(GR.HOUSE_GRP_NAME AS SIGNED),1 ) DESC,FL.STOREY_NUMBER DESC,H.HOUSE_NUMBER  DESC";
		if(null!=vo && null!=vo.getOrderName() && vo.getOrderName().trim().length()>0){
			if("inDate".equalsIgnoreCase(vo.getOrderName())){
				orderstr.append(" CR.IN_DATE ");
			}else if("outDate".equalsIgnoreCase(vo.getOrderName())){
				orderstr.append(" CR.OUT_DATE ");
			}else if("sysDate".equalsIgnoreCase((vo.getOrderName()))){
				orderstr.append(" PR.SYSDATE ");
			}else if("customerName".equalsIgnoreCase(vo.getOrderName())){
				orderstr.append(" CP.PARTICIPANT_NAME ");
			}else if("houseNumber".equalsIgnoreCase(vo.getOrderName())){
				if( "desc".equalsIgnoreCase(vo.getOrderKey()) ){
					orderstr.append(houseNumberOrderDesc);
				}else{
					orderstr.append(houseNumberOrderAsc);
				}
			}

			if(!"houseNumber".equalsIgnoreCase(vo.getOrderName())){
				orderstr.append(" ").append(vo.getOrderKey());
			}
		}else{
			orderstr.append(" ").append(houseNumberOrderAsc);
		}

		return orderstr.toString();
	}


	private String buildCondition(CustSearchVO vo){
		StringBuffer condtion=new StringBuffer();
		if(null!=vo && null!=vo.getHouseIds() && vo.getHouseIds().trim().length()>0){
			condtion.append(" AND CR.HOUSE_ID IN ( ").append(vo.getHouseIds()).append(" ) ") ;
		}
		if(null!=vo && null!=vo.getBeginInDate()){
			condtion.append(" AND CR.IN_DATE >= '").append(vo.getBeginInDate()).append("' ");
		}
		if(null!=vo && null!=vo.getEndInDate()){
			condtion.append(" AND CR.IN_DATE <= '").append(vo.getEndInDate()).append("' ");
		}
		if(null!=vo && null!=vo.getBeginOutDate()){
			condtion.append(" AND CR.OUT_DATE >= '").append(vo.getBeginOutDate()).append("' ");
		}
		if(null!=vo && null!=vo.getEndOutDate()){
			condtion.append(" AND CR.OUT_DATE <= '").append(vo.getEndOutDate()).append("' ");
		}
		if (null!=vo && null!= vo.getOnlyInDate() && vo.getOnlyInDate()) {
			condtion.append(" AND (CR.IN_DATE IS NOT NULL AND CR.OUT_DATE IS NULL) ");
		}
		return condtion.toString();
	}

}
